package software.lib.service;

import org.neo4j.driver.v1.util.Function;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Service;
import software.lib.util.NeoDriver;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Arrays;
import java.util.Vector;
import java.util.function.BiFunction;
import java.util.function.Consumer;

/**
 * Created by Wang on 2017/5/27.
 */

@Service
public class ClusterService {
    @Autowired
    private DataSource dataSource;

    /**
     * 计算gig之间的相似距离
     * @param costFunc 使用的相似函数
     * @param distanceColumn 将结果存入数据库的哪一列
     * @param columns 使用gig的哪些属性进行计算
     * @see Distance
     * @see Distance#chebyshev(Object[], Object[])
     * @see Distance#L1(Object[], Object[])
     * @see Distance#L2(Object[], Object[])
     * @see Distance#LN(double, Object[], Object[])
     * @see software.lib.util.functional.TriFunction#adaptFirst(Object)
     */
    public void computeAllDistance(BiFunction<Object[],Object[],Double> costFunc, String distanceColumn, String... columns){
        try(Connection conn = dataSource.getConnection()){
            conn.setAutoCommit(false);

            String sql = "SELECT DISTINCT * FROM gig AS gig1,gig AS gig2 WHERE gig2.gig_id<262903 AND gig1.gig_id<gig2.gig_id ";
            PreparedStatement queryAllStatement  = conn.prepareStatement(sql);
            PreparedStatement mergeDistanceStatement = conn.prepareStatement(
                    String.format("INSERT INTO gig_distance (gid1,gid2,%1$s) VALUES(?,?,?) "
                            + "ON DUPLICATE KEY UPDATE %1$s=VALUES(%1$s) "
                            ,distanceColumn));

            ResultSet resultSet = queryAllStatement.executeQuery();

            int index = 0;
            Vector<Object[]> vector=new Vector<>();
            while (resultSet.next()){
                Object[] gig1Vector = Arrays.stream(columns).map(column -> {
                    try {
                        return resultSet.getObject("gig1." + column);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    return null;
                }).toArray();

                Object[] gig2Vector = Arrays.stream(columns).map(column -> {
                    try {
                        return resultSet.getObject("gig2." + column);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    return null;
                }).toArray();

                int gid1 = resultSet.getInt("gig1.gig_id");
                int gid2 = resultSet.getInt("gig2.gig_id");

//                System.out.println(""+gid1+"\t"+gid2);

                vector.add(new Object[]{gid1,gid2,gig1Vector,gig2Vector});
//                Double distance = costFunc.apply(gig1Vector,gig2Vector);
//
//                mergeDistanceStatement.setInt(1,gid1);
//                mergeDistanceStatement.setInt(2,gid2);
//                mergeDistanceStatement.setDouble(3,distance);
//
//                mergeDistanceStatement.addBatch();
                index++;

                if (index>=ChockSize){
                    mergeInto(costFunc, conn, mergeDistanceStatement, vector);

                    index = 0;
                }
            }
            if(index>0){
                mergeInto(costFunc, conn, mergeDistanceStatement, vector);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void mergeInto(BiFunction<Object[], Object[], Double> costFunc, Connection conn, PreparedStatement mergeDistanceStatement, Vector<Object[]> vector) throws SQLException {
        vector.parallelStream().forEach(objects -> {
            Double distance = costFunc.apply((Object[])objects[2],(Object[])objects[3]);
            synchronized (mergeDistanceStatement) {
                try {
                    mergeDistanceStatement.setInt(1, (Integer) objects[0]);
                    mergeDistanceStatement.setInt(2, (Integer) objects[1]);
                    mergeDistanceStatement.setDouble(3, distance);

                    mergeDistanceStatement.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });

        vector.clear();

        mergeDistanceStatement.executeBatch();
        conn.commit();
        mergeDistanceStatement.clearBatch();
    }

    public static void main(String[] args) {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContextTest.xml");
        applicationContext.start();

        ClusterService clusterService = applicationContext.getBean(ClusterService.class);
//        clusterService.computeAllDistance(Distance::L1,"L1","score","favorite","timespan");
//        clusterService.computeAllDistance(Distance::L2,"L2","score","favorite","timespan");

        clusterService.convertDBTreeToNeo4j();
    }

    public void convertDBTreeToNeo4j(){
        try (NeoDriver driver = new NeoDriver();Connection conn = dataSource.getConnection()){
            PreparedStatement pTreeStatement = conn.prepareStatement("SELECT id,parent,`type`,`level` FROM treenode");
            PreparedStatement pTreeStatement2 = conn.prepareStatement("SELECT id,parent,`type` FROM treenode WHERE parent is not NULL ");
            PreparedStatement gigStatement = conn.prepareStatement("select gig_id,L1Parent,L2Parent from gig");

            ResultSet resultSet1 = pTreeStatement.executeQuery();
            while (resultSet1.next()){
                int nodeId = resultSet1.getInt("id");
                String method = resultSet1.getString("type");
                String level = resultSet1.getString("level");
                driver.query("create (n:TreeNode{id:"+nodeId+",method:'"+method+"',level:'"+level+"'}) return n");
            }
            ResultSet resultSet2 = gigStatement.executeQuery();
            while (resultSet2.next()){
                int gig_id = resultSet2.getInt("gig_id");
                int l1_parent_id = resultSet2.getInt("L1Parent");
                driver.query("match (g:gig{id:\"" + gig_id + "\"}),(n:TreeNode{id:"+l1_parent_id+"}) create (g)-[:cluster{type:'L1'}]->(n) ");
                int l2_parent_id = resultSet2.getInt("L2Parent");
                driver.query("match (g:gig{id:\"" + gig_id + "\"}),(n:TreeNode{id:"+l2_parent_id+"}) create (g)-[:cluster{type:'L2'}]->(n) ");
            }

            ResultSet resultSet3 = pTreeStatement2.executeQuery();
            while (resultSet3.next()){
                int nodeId = resultSet3.getInt("id");
                String method = resultSet3.getString("type");
                int parent_id = resultSet3.getInt("parent");
                driver.query("match (n:TreeNode{id:"+nodeId+"}),(p:TreeNode{id:"+parent_id+"}) create (n)-[:cluster{type:'"+method+"'}]->(p)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private static final int ChockSize=100;
}
